using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Generic;


public class DbUtils
{
    
    public static String ConnectionString { get; set; }

    public static bool TestConnection()
    {
        bool result = false;
        SqlConnection connection = new SqlConnection(ConnectionString);
        try
        {
            result = true;
            connection.Open();

        }
        catch
        {
            result = false;
        }
        finally
        {
            connection.Close();
        }
        return result;
    }
    public static DataSet ExecuteDataSet(string storedProcedureName, params KeyValuePair<string, object>[] parameters)
    {
        DataSet result = new DataSet();

        SqlConnection connection = new SqlConnection(ConnectionString);
        try
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand(storedProcedureName, connection);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (KeyValuePair<string, object> pair in parameters)
            {
                cmd.Parameters.AddWithValue(pair.Key, pair.Value);
            }
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable scheme = reader.GetSchemaTable();
            if (scheme != null)
            {
                DataTable table = new DataTable();
                for (int i = 0; i < scheme.Rows.Count; i++)
                {
                    DataColumn column = new DataColumn(
                        (string)scheme.Rows[i]["ColumnName"], 
                        (Type)scheme.Rows[i]["DataType"]);

                    table.Columns.Add(column);
                }
                result.Tables.Add(table);

                while (reader.Read())
                {
                    DataRow row = table.NewRow();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row[i] = reader.GetValue(i);
                    }
                    table.Rows.Add(row);
                }
            }    
        }
        catch (SqlException ex)
        {
            result = null;
        }
        finally
        {
            connection.Close();
        }

        return result;
    }

}
